﻿using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using UpLoadDataOnTime.DataBaseUtil;
using UpLoadDataOnTime.Entity;

namespace UpLoadDataOnTime.Model
{
    class GetZYData
    {
        IDatabase db = DbFactory.CreateDb();

        //读取配置文件中的连接字符串（需添加引用System.Configuration）
        private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        private static string connStrP = ConfigurationManager.ConnectionStrings["dbConnStrP"].ConnectionString;
        private static string connStrL = ConfigurationManager.ConnectionStrings["dbConnStrL"].ConnectionString;

        //住院患者基本信息
        public List<ZYTbPatientInfo> GetZYTbPatientInfos(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                                   " +
                         "       '09' AS GRBSLX,                                            " +
                         "       '43120477700' + '_' + RTRIM(PatientID) AS GRBSH,           " +
                         "       '01' AS ZJLX,                                              " +
                         "       ISNULL(M08,'未上传') AS ZJHM,                              " +
                         "       '云南省第三人民医院' AS YYMC,                               " +
                         "       CASE M05 WHEN '男' THEN '1'                                " +
                         "                WHEN '女' THEN '2'                                " +
                         "       ELSE '9'                                                   " +
                         "       END AS XB,                                                 " +
                         "       M04 AS XM,                                                 " +
                         "       CASE b.FP8 WHEN '1' THEN '10'                              " +
                         "                  WHEN '2' THEN '20'                              " +
                         "                  WHEN '3' THEN '30'                              " +
                         "                  WHEN '4' THEN '40'                              " +
                         "       ELSE '90'                                                  " +
                         "       END AS HYZK,                                               " +
                         "       CONVERT(VARCHAR(10),M06,121) AS CSRQ,                      " +
                         "       ISNULL(b.FP17,'未上传') AS CSD,                            " +
                         "       ISNULL(b.FP17_Name,'未上传') AS CSDMC,                     " +
                         "       ISNULL((CASE WHEN FP11 = '99' THEN NULL ELSE FP11 END),'01') AS MZ," +
                         "       '156' AS GJ,                                               " +
                         "       a.M09 AS SJHM                                              " +
                         "FROM dbo.MBASE a(NOLOCK)                                          " +
                         "JOIN dbo.BA_FPage b(NOLOCK) ON CONVERT(VARCHAR(15),a.M01) = b.FP0 " +
                         "WHERE a.M11 >= @BeginDate AND a.M11 < @EndDate                    " +
                         "AND a.M52 IS NOT NULL                                             ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            List<ZYTbPatientInfo> list = db.Context.QueryMany<ZYTbPatientInfo>(connStr, sql, parameters);
            return list;
        }

        //住院记录信息
        public List<ZYTbHospitalizationRecord> GetZYTbHospitalizationRecords(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                   " +
                         "       FP0 AS JZLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                " +
                         "       '09' AS GRBSLX,                             " +
                         "       '43120477700' + '_' + FP_PatientID AS GRBSH," +
                         "       CASE FP93 WHEN '6' THEN '5'                 " +
                         "       ELSE FP93                                   " +
                         "       END AS ABOXXDM,                             " +
                         "       FP87  AS RHXXDM,                            " +
                         "       CONVERT(VARCHAR(10),FP25,121) AS RYRQSJ,    " +
                         "       CONVERT(VARCHAR(10),FP31,121) AS CYRQSJ,    " +
                         "       '未上传' AS XBS,                                " +
                         "       '未上传' AS JWS,                                " +
                         "       '未上传' AS GMS,                                " +
                         "       '未上传' AS HYS                                 " +
                         "FROM dbo.BA_FPage (NOLOCK)                         " +
                         "WHERE FP31 >= @BeginDate AND FP31 < @EndDate " +
                         "ORDER BY FP31                                      ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            List<ZYTbHospitalizationRecord> list = db.Context.QueryMany<ZYTbHospitalizationRecord>(connStr, sql, parameters);
            return list;
        }

        //出院小结信息
        public List<ZYTbDischargeAbstract> GetZYTbDischargeAbstracts(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                   " +
                         "       FP0 AS JZLSH,                               " +
                         "       '云南省第三人民医院' AS YYMC,                " +
                         "       '09' AS GRBSLX,                             " +
                         "       '43120477700' + '_' + FP_PatientID AS GRBSH," +
                         "       FP29 AS KSBH,                               " +
                         "       FP29_NAME AS KSMC,                          " +
                         "       CONVERT(VARCHAR(10),FP25,121) AS RYSJ,      " +
                         "       CONVERT(VARCHAR(10),FP31,121) AS CYSJ,      " +
                         "       CONVERT(INT,FP33) AS ZYTS,                  " +
                         "       FP36_YS AS MZZD,                            " +
                         "       FP37_YS AS RYZD,                            " +
                         "       FP37_NAME AS CYZD,                          " +
                         "       '未上传' AS CYYZ,                           " +
                         "       FP118_ZZYS AS ZZYSXM                        " +
                         "FROM dbo.BA_FPage (NOLOCK)                         " +
                         "WHERE FP31 >= @BeginDate AND FP31 < @EndDate       " +
                         "ORDER BY FP31                                      ";
            SqlParameter[] parameters = new SqlParameter[]
          {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
          };
            List<ZYTbDischargeAbstract> list = db.Context.QueryMany<ZYTbDischargeAbstract>(connStr, sql, parameters);
            return list;
        }

        //住院手术信息
        public List<ZYTbOperation> GetZYTbOperations(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM ,                           " +
                         "       KeyNo AS SSMXLSH,                                   " +
                         "       '云南省第三人民医院' AS YYMC,                        " +
                         "       ZYNum AS JZLSH,                                     " +
                         "       '09' AS GRBSLX,                                     " +
                         "       '43120477700' + '_' + b.FP_PatientID AS GRBSH,      " +
                         "       '2' AS MZZYBZ,                                      " +
                         "       '9' AS SSLX,                                        " +
                         "       a.OperationCode AS SSCZDM,                          " +
                         "       a.OperationName AS SSCZMC,                          " +
                         "       FP41 AS SSQZD,                                      " +
                         "       '未上传' AS SSHZD,                                   " +
                         "       CONVERT(VARCHAR(10),a.OperationDate,121) AS SSKSSJ, " +
                         "       CONVERT(varchar,GETDATE(),120) AS SSJSSJ,           " +
                         "       c.CODE AS SSYSBH,                                   " +
                         "       a.OperationOperName AS SSYSXM,                      " +
                         "       CASE a.CutCode WHEN '4' THEN '1'                    " +
                         "                      WHEN '5' THEN '2'                    " +
                         "                      WHEN '6' THEN '3'                    " +
                         "                      WHEN '7' THEN '1'                    " +
                         "                      WHEN '8' THEN '2'                    " +
                         "                      WHEN '9' THEN '3'                    " +
                         "                      WHEN '0' THEN '4'                    " +
                         "                      WHEN '10' THEN '1'                   " +
                         "                      WHEN '11' THEN '2'                   " +
                         "                      WHEN '12' THEN '3'                   " +
                         "       ELSE '4'                                            " +
                         "       END AS QKYHDJ                                       " +
                         "FROM dbo.BA_Operation a(NOLOCK)                            " +
                         "JOIN dbo.BA_FPage b(NOLOCK)ON a.ZYNum = b.FP0              " +
                         "JOIN dbo.YSCODE c(NOLOCK) ON a.OperationOperName = c.NAME  " +
                         "WHERE b.FP31 >= @BeginDate AND b.FP31 < @EndDate     ";
            SqlParameter[] parameters = new SqlParameter[]
         {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
         };
            List<ZYTbOperation> list = db.Context.QueryMany<ZYTbOperation>(connStr, sql, parameters);
            return list;
        }

        //住院检查信息
        public List<ZYTbCheckRecord> GetZYTbCheckRecords(string BeginDate, string EndDate)
        {
            string sql = "SELECT '43120477700' AS YLJGDM,                              " +
                         "       '云南省第三人民医院' AS YYMC,                          " +
                         "       AccessionNumber AS JCBGID,                            " +
                         "       InPatientNum AS JZLSH,                                " +
                         "       '09' AS GRBSLX,                                       " +
                         "       '43120477700' + '_' + PatientID_His AS GRBSH,         " +
                         "       '2' AS MZZYBZ,                                        " +
                         "       '3' AS hzlxdm,                                        " +
                         "       ISNULL(CONVERT(VARCHAR(10),PatientStudyDate,120),CONVERT(VARCHAR(10),GETDATE(),120)) AS JCRQ,  " +
                         "       ISNULL(ReportDoctor,'未上传') AS JCYSQM,                               " +
                         "       ClinicName AS BGJGMC,                                 " +
                         "       ClinicName AS BGKSMC,                                 " +
                         "       ISNULL(ReportDoctor,'未上传') AS BGYSQM,               " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGKGSJ,         " +
                         "       ISNULL(DiagnosesText,'未上传') AS JCBGJGZGTS,         " +
                         "       CASE ModalityType WHEN 'DR' THEN '04'                 " +
                         "                         WHEN 'CT' THEN '01'                 " +
                         "                         WHEN 'B超' THEN '06'                " +
                         "                         WHEN '1胃肠镜' THEN '08'            " +
                         "                         WHEN '5胃肠镜' THEN '08'            " +
                         "                         WHEN 'B超-JR' THEN '06'             " +
                         "                         WHEN 'MG' THEN '10'                 " +
                         "                         WHEN 'MR' THEN '02'                 " +
                         "                         WHEN 'OT' THEN '10'                 " +
                         "                         WHEN 'US' THEN '06'                 " +
                         "                         WHEN '病理' THEN '07'               " +
                         "                         WHEN '肠镜' THEN '08'               " +
                         "                         WHEN '电子气管镜' THEN '08'         " +
                         "                         WHEN '胃肠镜' THEN '08'             " +
                         "       ELSE '10'                                             " +
                         "       END AS JCLX,                                          " +
                         "       Modality AS YQBM,                                     " +
                         "       ClinicName AS JCKSMC,                                 " +
                         "       CASE ScanWay WHEN '' THEN '未上传' ELSE ScanWay END AS JCBW,  " +
                         "       BodyPartExamined AS JCMC,                             " +
                         "       CASE WHEN Abnormality IS NULL THEN 2                  " +
                         "            WHEN Abnormality = 2 THEN 2                      " +
                         "            WHEN Abnormality = 3 THEN 3                      " +
                         "       ELSE Abnormality                                      " +
                         "       END AS YYS,                                           " +
                         "       DiagnosesText AS BGLCZD,                              " +
                         "       DiagnosesText AS YXBX,                                " +
                         "       DiagnosesText AS YXZD,                                " +
                         "       '1' AS SFYYY                                          " +
                         "FROM PatientSchedule a(NOLOCK)                               " +
                         "JOIN dbo.PatientBase b(NOLOCK) ON b.PatientID = a.PatientID  " +
                         "WHERE DelDate IS NULL                                        " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND InPatientNum IS NOT NULL                                " +
                         "AND InPatientNum <> ''                                      " +
                         "AND ReportDate IS NOT NULL                                   " +
                         "AND ReportDate >= @BeginDate AND ReportDate < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
        {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
        };
            List<ZYTbCheckRecord> list = db.Context.QueryMany<ZYTbCheckRecord>(connStrP, sql, parameters);
            return list;
        }

        //住院检验信息
        public List<ZYTbAssayRecord> GetZYTbAssayRecords(string BeginDate, string EndDate)
        {
            string sql = @"select '43120477700' as YLJGDM,                                                                                                                       
                                CONVERT(char(8),a.date_test,112) + a.YBH as JYBGID,                                                                                              
                                '云南省第三人民医院' AS YYMC,                                                                                                                       
                                a.Patient_Code as JZLSH,                                                                                                                         
                                '09' as GRBSLX,                                                                                                                                  
                                '43120477700' + '_' + a.Patient_Code as GRBSH,                                                                                                   
                                CASE a.patient_kind WHEN '1' THEN a.patient_kind                                                                                                 
                                                    WHEN '2' THEN '1'                                                                                                            
                                                    WHEN '0' THEN '2'                                                                                                            
                                                    WHEN '8' THEN '2'                                                                                                            
                                ELSE '9'                                                                                                                                         
                                END as MZZYBZ,                                                                                                                                   
                                (case a.Patient_Kind  when '1' then a.Patient_Code when '2' then a.Patient_Code else NULL end) as MJZH,                                          
                                (case a.Patient_Kind  when '0' then a.Patient_Code when '8' then a.Patient_Code else NULL end)  as ZYH,                                          
                                CASE ISNULL(a.patient_kind,NULL) WHEN '1' THEN a.patient_kind                                                                                    
                                                               WHEN '2' THEN a.patient_kind                                                                                      
                                                               WHEN '0' THEN '3'                                                                                                 
                                                               WHEN '8' THEN '3'                                                                                                 
                                ELSE '4'                                                                                                                                         
                                END as HZLXDM,                                                                                                                                   
                                A.YBH AS JYBBH,                                                                                                                                  
                                d.Name as JYXMMC,                                                                                                                                
                                (select name from ut_dict_user where User_ID = A.reporter) as JYYSQM,                                                                            
                                CONVERT(VARCHAR(10),A.Date_Check,120) AS JYRQ, 
                                ISNULL((select Group_Name from UT_DICT_Group  where Group_Code =(select check_group from ut_dict_report  where code = LEFT(A.YBH,2))),'未上传') AS BGKSMC, 
                                '未上传' AS BGYSQM, 
                                '未上传' AS JYBGJG,                                                                                                                               
                                CASE A.Kind WHEN '+7' THEN '0032'
                                            WHEN '1' THEN '0002'
                                            WHEN '10' THEN '0043'
                                            WHEN '11' THEN '0051'
                                            WHEN '12' THEN '0048'
                                            WHEN '13' THEN '0047'
                                            WHEN '14' THEN '0041'
                                            WHEN '15' THEN '0054'
                                            WHEN '17' THEN '0039'
                                            WHEN '18' THEN '0035'
                                            WHEN '19' THEN '0046'
                                            WHEN '2' THEN '0004'
                                            WHEN '20' THEN '0024'
                                            WHEN '22' THEN '0051'
                                            WHEN '23' THEN '0044'
                                            WHEN '26' THEN '0034'
                                            WHEN '29' THEN '0051'
                                            WHEN '3' THEN '0001'
                                            WHEN '30' THEN '0037'
                                            WHEN '31' THEN '0007'
                                            WHEN '32' THEN '0002'
                                            WHEN '33' THEN '0037'
                                            WHEN '34' THEN '0025'
                                            WHEN '35' THEN '9999'
                                            WHEN '36' THEN '0023'
                                            WHEN '4' THEN '0003'
                                            WHEN '40' THEN '0054'
                                            WHEN '5' THEN '0021'
                                            WHEN '54' THEN '0051'
                                            WHEN '56' THEN '0051'
                                            WHEN '6' THEN '0030'
                                            WHEN '61' THEN '0031'
                                            WHEN '62' THEN '0050'
                                            WHEN '64' THEN '0021'
                                            WHEN '7' THEN '0031'
                                            WHEN '73' THEN '0050'
                                            WHEN '75' THEN '0054'
                                            WHEN '76' THEN '0021'
                                            WHEN '8' THEN '0036'
                                            WHEN '95' THEN '0049'
                                 ELSE '9999'
                                 END AS BBDM,                                                                                                                                   
                                (select Kind_Name from UT_Check_Module_Kind where Kind_Code = a.kind ) AS BBMC,                                                                  
                                ISNULL((select check_group from ut_dict_report  where code = LEFT(A.YBH,2)),'9999') AS BGDLBBM,                                                  
                                 (select report_name from ut_dict_report where code = LEFT(A.YBH,2)) AS BGDLB                                                                    
                         from   ut_check_patient a(NOLOCK) left outer join ut_check_collection c (nolock) on a.date_test = c.date_test and a.ybh = c.ybh, UT_DICT_Project_Kind d 
                         WHERE  a.Flag_Send ='5' and c.Item_Check = d.Code                                                                                                       
                         AND a.Collection_time >= @BeginDate AND a.Collection_time < @EndDate                                                                                    
                         AND a.patient_kind IN ('0','8',NULL)                                                                                                                    ";
             SqlParameter[] parameters = new SqlParameter[]
             {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
             };
                List<ZYTbAssayRecord> list = db.Context.QueryMany<ZYTbAssayRecord>(connStrL, sql, parameters);
                return list;
        }

        //住院检验结果指标信息
        public List<ZYTbAssayQuotaResult> GetZYTbAssayQuotaResults(string BeginDate,string EndDate)
        {
            string sql = "SELECT     '43120477700' as YLJGDM,                                                                                                                                                       " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYZBLSH,                                                                                                                           " +
                         "           '云南省第三人民医院' AS YYMC,                                                                                                                                                    " +
                         "           convert(char(8),r.test_date,112) + r.YBH AS JYBGID,                                                                                                                            " +
                         "           convert(VARCHAR(10),r.test_date,121) as BGRQ,                                                                                                                                  " +
                         "           (select name from dbo.ut_dict_user where user_id = p.reporter) as JCRXM,                                                                                                       " +
                         "           r.item_code as YBSFDM,                                                                                                                                                         " +
                         "           'ZZZZZZZZZZZZZZZ' AS YZMXBMYB,                                                                                                                                                 " +
                         "CASE (select test_mothod_name from ut_dict_method where test_mothod in(select test_method from ut_dict_items where item_code=r.item_code)) WHEN '' THEN '未上传' ELSE ISNULL((select test_mothod_name from ut_dict_method where test_mothod in(select test_method from ut_dict_items where item_code=r.item_code)),'未上传') END AS JCFFMC, " +
                         "CASE (select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code) WHEN '' THEN '未上传' ELSE ISNULL((select Item_Name from dbo.UT_DICT_Items where Item_Code=r.Item_Code),'未上传') END AS JCZBMC,                                                                              " +
                         "            case (select Value_Type from dbo.UT_DICT_Items where Item_Code = r.Item_Code) when '1' then '1' when '2' then '2' when '3' then '2' when '4' then '2' else '9' end as ZBJGLX, " +
                         "           r.Test_Value2 as JCJGZB,                                                                                                                                                       " +
                         "           dbo.fun_share_midat(r.Text_range,1,'未上传') as CKSX,                                                                                                                               " +
                         "           dbo.fun_share_midat(r.Text_range,0,'未上传') as CKXX,                                                                                                                               " +
                         "           r.Test_Value2 AS DXCKJG,                                                                                                                                                       " +
                         "           '项' as JLDW                                                                                                                                                                   " +
                         "FROM       dbo.UT_Check_Result AS r,dbo.ut_check_patient as p                                                                                                                             " +
                         "WHERE      p.Date_test = r.Test_Date and p.YBH = r.YBH and p.flag_send = '5'                                                                                                              " +
                         "AND r.Test_Date >= @BeginDate AND r.Test_Date < @EndDate ";
            SqlParameter[] parameters = new SqlParameter[]
           {
                new SqlParameter("@BeginDate",BeginDate),
                new SqlParameter("@EndDate",EndDate)
           };
            return db.Context.QueryMany<ZYTbAssayQuotaResult>(connStrL, sql, parameters);
        }
    }
}
